package com.mbc.common.dao;

import java.sql.CallableStatement;
import java.sql.Connection;
import java.sql.ResultSet;
import java.sql.SQLException;
import java.util.ArrayList;

import com.mbc.common.entities.Country;
import com.mbc.common.entities.CountryRpt;
import com.mbc.common.data.CountryDataList;

public class CountryDAOImpls extends DAO<Country> implements CountryDAO {

	public CountryDAOImpls(Connection conn) {
		this.conn = conn;
		this.table_name = "Country";
	}

	@Override
	public void create(Country entity) throws SQLException {
		query = "insert into "
				+ table_name
				+ " ( CountryId, CountryCode, Name) values( ?, ?, ?)";
		pstmt = conn.prepareStatement(query);
		int i = 1;
		pstmt.setLong(i++, entity.getCountryId());
		pstmt.setString(i++, entity.getCountryCode());
		pstmt.setString(i++, entity.getName());
		pstmt.executeUpdate();
		pstmt.close();

	}

	@Override
	public void update(Country entity) throws SQLException {
		query = "update " + table_name
				+ " set CountryCode = ?, Name = ?"
				+ " where CountryId = " + entity.getCountryId();
		pstmt = conn.prepareStatement(query);
		int i = 1;
		pstmt.setString(i++, entity.getCountryCode());
		pstmt.setString(i++, entity.getName());
		pstmt.executeUpdate();
		pstmt.close();

	}

	@Override
	public void delete(long key) throws SQLException {
		query = getDeleteQuery() + " where CountryId = " + key;
		pstmt = conn.prepareStatement(query);
		pstmt.executeUpdate();
		pstmt.close();

	}

	@Override
	public Country find(long key) throws SQLException {
		Country l_Country = null;
		query = getSelectQuery() + " where CountryId = " + key;
		pstmt = conn.prepareStatement(query);
		rs = pstmt.executeQuery();
		if (rs.next())
			l_Country = readRecord(rs);
		rs.close();
		pstmt.close();
		return l_Country;
	}

	@Override
	public CountryDataList findCountry(Country cri) throws SQLException {
		CountryDataList l_DataList = new CountryDataList();
		ArrayList<Country> l_entityList = new ArrayList<Country>();
		/*String l_cri = " and (CountryCode like N'%" + cri.getKeyword() + "%'"
		+ " or Name like N'%" + cri.getKeyword() + "%')";*/

		String l_cri = " and ( " + cri.getFieldname() + " like N'%" + cri.getKeyword() + "%')";
		
		query = "set rowcount "
				+ cri.getSize()
				+ " select * from (select row_number() over(order by CountryId) as [rowno], * from "
				+ table_name + " where 1 = 1" + l_cri + ") " + table_name
				+ " where rowno > " + cri.getIndex();
		pstmt = conn.prepareStatement(query);
		rs = pstmt.executeQuery();
		while (rs.next()) {
			l_entityList.add(readRecord(rs));
		}
		l_DataList.setEntityList(l_entityList);
		rs.close();
		pstmt.close();
		query = "select count(CountryId) from " + table_name + " where 1 = 1"
				+ l_cri;
		pstmt = conn.prepareStatement(query);
		rs = pstmt.executeQuery();
		if (rs.next()) {
			l_DataList.setTotalRecord(rs.getLong(1));
		}
		rs.close();
		pstmt.close();
		return l_DataList;
	}

	@Override
	public ArrayList<Country> findAll() throws SQLException {
		ArrayList<Country> l_CountryList = new ArrayList<Country>();
		query = getSelectQuery();
		pstmt = conn.prepareStatement(query);
		rs = pstmt.executeQuery();
		while (rs.next()) {
			l_CountryList.add(readRecord(rs));
		}
		rs.close();
		pstmt.close();
		return l_CountryList;
	}

	@Override
	public Country findCountryByCode(Country entity) throws SQLException {
		Country l_Country = null;
		query = getSelectQuery() + " where 1 = 1";
		
		if(!entity.getCountryCode().equals("")) {
			query += " and CountryCode = '" + entity.getCountryCode() + "'";
		}
		if(!entity.getName().equals("")){
		    query += " and Name = '" + entity.getName() + "'";
		}
		pstmt = conn.prepareStatement(query);
		rs = pstmt.executeQuery();
		if (rs.next())
			l_Country = readRecord(rs);
		rs.close();
		pstmt.close();
		return l_Country;
	}

	@Override
	public ResultSet prepareCountryReport(CountryRpt info) throws SQLException {
		ResultSet l_rs;
		String l_query = "dbo.sp_cr_CountryRpt ?, ?";

		CallableStatement l_pstmt = conn.prepareCall(l_query);
		CountryRptParameter(l_pstmt, info);
		l_rs = l_pstmt.executeQuery();
		return l_rs;
	}

	private void CountryRptParameter(CallableStatement l_pstmt, CountryRpt info)
			throws SQLException {
		int i = 1;

		l_pstmt.setString(i++, info.getFromCountryID());
		l_pstmt.setString(i++, info.getToCountryID());
	}
	@Override
	protected Country readRecord(ResultSet rs) throws SQLException {
		Country l_entity = new Country();
		l_entity.setCountryId(rs.getLong("CountryId"));
		l_entity.setCountryCode(rs.getString("CountryCode"));
		l_entity.setName(rs.getString("Name"));
		return l_entity;
	}
	

}
